package org.jcnc.staffingsystem.db;

import java.sql.*;

public class EmployeeManager {

    // JDBC连接参数
    private static final String URL = "jdbc:mysql://localhost:3306/employee_info";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    // 插入员工信息
    public void insertEmployee(String name, String employeeId, String gender, String department, String phone,
                               String position, String address, String account) {
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "INSERT INTO employees (name, emp_id, gender, department, phone, position, address, account) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, name);
                pstmt.setString(2, employeeId);
                pstmt.setString(3, gender);
                pstmt.setString(4, department);
                pstmt.setString(5, phone);
                pstmt.setString(6, position);
                pstmt.setString(7, address);
                pstmt.setString(8, account);

                pstmt.executeUpdate();
                System.out.println("Employee inserted successfully.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 修改员工信息（根据账号）
    public void updateEmployeeByAccount(String name, String jobNumber, String sex, String department, String phoneNumber, String posts, String address, String account) {
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "UPDATE employees SET name = ?, emp_id = ?, gender = ?, department = ?, phone = ?, position = ?, address = ? WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, name);
                pstmt.setString(2, jobNumber);
                pstmt.setString(3, sex);
                pstmt.setString(4, department);
                pstmt.setString(5, phoneNumber);
                pstmt.setString(6, posts);
                pstmt.setString(7, address);
                pstmt.setString(8, account);

                int rowsAffected = pstmt.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("Employee information updated successfully.");
                } else {
                    System.out.println("No employee found with the provided account.");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    // 查找员工信息（根据账号）
    public void findEmployeeByAccount(String account) {
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "SELECT * FROM employees WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, account);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        String name = rs.getString("name");
                        String gender = rs.getString("gender");
                        String department = rs.getString("department");
                        String phone = rs.getString("phone");
                        String position = rs.getString("position");
                        String address = rs.getString("address");

                        System.out.println("Account: " + account);
                        System.out.println("Name: " + name);
                        System.out.println("Gender: " + gender);
                        System.out.println("Department: " + department);
                        System.out.println("Phone: " + phone);
                        System.out.println("Position: " + position);
                        System.out.println("Address: " + address);
                    } else {
                        System.out.println("No employee found with the provided account.");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据账号查找员工性别信息
    public String findEmployeeGenderByAccount(String account) {
        String gender = "";
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "SELECT gender FROM employees WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, account);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        gender = rs.getString("gender");
                        System.out.println("Employee gender for account " + account + ": " + gender);
                    } else {
                        System.out.println("No employee found with the provided account.");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return gender;
    }

    public String findEmployeeNameByAccount(String account) {
        String name = "";
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "SELECT name FROM employees WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, account);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        name = rs.getString("name");
                        System.out.println("Employee name for account " + account + ": " + name);
                    } else {
                        System.out.println("No employee found with the provided account.");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return name;
    }

    public String findEmployeeEmp_idByAccount(String account) {
        String emp_id = "";
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "SELECT emp_id FROM employees WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, account);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        emp_id = rs.getString("emp_id");
                        System.out.println("Employee emp_id for account " + account + ": " + emp_id);
                    } else {
                        System.out.println("No employee found with the provided account.");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return emp_id;
    }

    public String findEmployeeDepartmentByAccount(String account) {
        String department = "";
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "SELECT department FROM employees WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, account);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        department = rs.getString("department");
                        System.out.println("Employee department for account " + account + ": " + department);
                    } else {
                        System.out.println("No employee found with the provided account.");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return department;
    }

    public String findEmployeePhoneByAccount(String account) {
        String phone = "";
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "SELECT phone FROM employees WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, account);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        phone = rs.getString("phone");
                        System.out.println("Employee phone for account " + account + ": " + phone);
                    } else {
                        System.out.println("No employee found with the provided account.");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return phone;
    }

    public String findEmployeeAddressByAccount(String account) {
        String address = "";
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "SELECT address FROM employees WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, account);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        address = rs.getString("address");
                        System.out.println("Employee address for account " + account + ": " + address);
                    } else {
                        System.out.println("No employee found with the provided account.");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return address;
    }

    public String findEmployeePositionByAccount(String account) {
        String position = "";
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String sql = "SELECT position FROM employees WHERE account = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, account);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        position = rs.getString("position");
                        System.out.println("Employee position for account " + account + ": " + position);
                    } else {
                        System.out.println("No employee found with the provided account.");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return position;
    }

    public static void main(String[] args) {
        EmployeeManager employeeManager = new EmployeeManager();

        // 插入员工信息
        employeeManager.insertEmployee("John 1", "1001", "Male", "IT", "1234567890",
                "Developer", "123 Main St", "1");

        // 更新员工信息（根据账号）
//        employeeManager.updateEmployeeByAccount("johndoe", "Finance");

        // 查找员工信息（根据账号）
//        employeeManager.findEmployeeByAccount("johndoe");
    }
}
